package system;

import java.util.ArrayList;
import java.util.List;
import java.sql.*;

import entities.Parameter;
import entities.ParameterDetail;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import common.Utility;
import connect.ConnectionHelper;


public class ParameterService {			
	
	/**
	 * get parameter
	 * @return
	 * @throws Exception
	 */
	public List<Parameter> getParameterByID(String id) throws Exception {

		List<Parameter> list = new ArrayList<Parameter>();
		Connection c = null;
		try {										
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();			
			ResultSet rs = s.executeQuery("SELECT id,name,status,usercreate,datecreate,usermodify,datemodify FROM parameter WHERE upper(name) like upper('%" + id + "%') ORDER BY id");			
			while (rs.next()) {
				list.add(new Parameter(rs.getString("id"),
						rs.getString("name"),
						rs.getString("status"),
						rs.getString("usercreate"),
						Utility.formatSqlAsSimpleDate(rs.getDate("datecreate"), "dd/MM/yyyy"),
						rs.getString("usermodify"),
						Utility.formatSqlAsSimpleDate(rs.getDate("datemodify"), "dd/MM/yyyy")));
			}			
		} catch (SQLException e) {
			e.printStackTrace();			
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;
	}		
	
	/**
	 * get parameter detail
	 * @param strID : detail info
	 * @return
	 * @throws Exception
	 */
	public List<ParameterDetail> getParameterDetail(String id) throws Exception {

		List<ParameterDetail> list = new ArrayList<ParameterDetail>();
		Connection c = null;
		try {										
			c = ConnectionHelper.getConnection();						
			Statement s = c.createStatement();			
			ResultSet rs = s.executeQuery("SELECT id,name,status,sysname FROM PARAMETERDETAIL WHERE ID = '" + id + "' ORDER BY name");			
			while (rs.next()) {
				list.add(new ParameterDetail(rs.getString("id"),
						rs.getString("name"),
						rs.getString("status"),
						rs.getString("sysname")));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();			
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);
		}
		return list;
	}
	
	public String deleteParamDetail (String paramID, String paramName, String paramDetailList, String userAction) throws Exception {
		String strResult = "0";
		Connection c = null;		
		try
		{			
			c = ConnectionHelper.getConnection();							
			CallableStatement proc_stmt = null;			
			proc_stmt = c.prepareCall("{ call paramdetaildelete(?,?,?,?,?) }");
            
            proc_stmt.setString(1, paramID);
            proc_stmt.setString(2, paramName);
            proc_stmt.setString(3, paramDetailList);
            proc_stmt.setString(4, userAction);             
            proc_stmt.registerOutParameter(5, Types.VARCHAR);
            proc_stmt.executeQuery();            
            strResult = proc_stmt.getString(5);                
             																														
		} catch (SQLException e) {
			strResult = "Error";
			e.printStackTrace();		
			throw new Exception(e);
		} finally {
			ConnectionHelper.close(c);		
		}
		//System.out.println(strResult);
		return strResult;
	}	
}
